package demo.mapper;

import com.github.pagehelper.Page;
import demo.pojo.Users;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import tk.mybatis.mapper.common.Mapper;

@org.apache.ibatis.annotations.Mapper
public interface UserMapper extends Mapper<Users> {

    @Select("SELECT users.phone,users.id,users.province_id as provinceId,users.name,users.birthday,CONCAT(p.name,p1.name,p2.name) AS address FROM province p,province p1,province p2,users WHERE users.province_id=p2.id AND p.id=p1.parent_id  AND p1.id=p2.parent_id")
    Page<Users> findAll();

    @Select("SELECT t1.id,t1.phone,t1.name,t1.birthday,t2.* FROM users t1,(SELECT a.id province,b.id city,c.id county FROM province a,province b,province c WHERE a.id = b.parent_id AND b.id = c.parent_id) t2 WHERE t1.province_id = t2.county AND t1.id = #{id}")
    Users findId(Integer id);

    @Update("update users set name=#{name},birthday=#{birthday},province_id=#{provinceId},phone=#{phone} where id = #{id}")
    void edit(Users users);

    @Insert("insert into users values(null,#{name},#{birthday},#{provinceId},#{phone})")
    void add(Users users);

    @Delete("delete from users where id=#{id}")
    void del(Integer id);
}
